# imports
import pandas as pd
import numpy as np
import datetime as dt

# sql stuff
import sqlalchemy as sq
import csv
from sqlalchemy import text
import mysql.connector
from mysql.connector import Error

# plotting
import matplotlib.pyplot as plt
import plotly.graph_objects as go
from plotly.subplots import make_subplots
import plotly.express as px
import statsmodels

# connection details like password etc.
PASSWORD = "49dw2Wuyku6Df"
USERNAME = "student"
HOST = "localhost"
PORT = "3306"
DATABASENAME = "student"

# change these to your filepath
LABOURPATH = "Data/labour_force_data.csv"
CPIPATH = "Data/CPI_dataset.csv"
OPIATEPATH = "Data/opioid_data.csv"
DEATHSPATH = "Data/deaths.csv"
GEOJSONPATH = 'Data/canada-provinces.geojson'

#Creating engine
engine = sq.create_engine(f'mysql+mysqlconnector://{USERNAME}:{PASSWORD}@{HOST}:{PORT}/{DATABASENAME}')

def SQL(myquerystring):
    """ shorthand for pandas ready query """
    return pd.read_sql_query(myquerystring, engine)

# Noah's function definitions.
def pdsql(myquery) -> pd.DataFrame:
    """
    run SQL query and returns a dataframe
    """
    try:
        return pd.read_sql_query(myquery, engine)
    except Exception as e:
        return repr(e)

def runsql(command) -> None:
    """
    run SQL command in general. this one does not return a dataframe
    """
    try:
        with engine.connect() as cxn:
            cxn.execute(sq.text(command))
    except Exception as e:
        return repr(e)
    finally:
        return None

def droptable(tablename, needconfirmation=True) -> None:
    """
    tells SQL to drop the table if it exists already. asks for confirmation
    """
    # ask for confirmation
    if needconfirmation:
        areyousure = input(f'To drop table "{tablename}", type YES: ')
    else: # if running this function with ask=False
        areyousure = "YES"

    # execute drop table command
    if areyousure == "YES":
        runsql(f"DROP TABLE IF EXISTS {tablename};")
        print(f'table "{tablename}" dropped (or doesnt exist)')
    else:
        print(f'Wrong input, table "{tablename}" not dropped.')

    return None

def uploadLABOURdatabychunk(
    chunksize : int = 10**6,
    filename : str = "Data/14100287.csv",
    tablename : str = "labour",
    details : bool = True) -> None:
    """
    Read in the csv and upload to sql table in chunks.
    """

    # if we are running this function, want to make sure the table doesnt have data already
    droptable(tablename, needconfirmation=False)

    # open csv file to pandas dataframe in chunks. parse dates properly
    with pd.read_csv(filename,
                     parse_dates=['REF_DATE'],
                     date_format='%Y-%m',
                     chunksize=chunksize,
                     low_memory=False
                    ) as reader:
        # with file open and chunked, go through each chunk
        for i, chunk in enumerate(reader):
            """
            drop columns with only null values, or all the same value, or duplicate info:
            -DGUID is the same info as GEO
            -UOM_ID is the same info as UOM
            -SCALAR_FACTOR is either thousands if UOM is persons,
                or units if UOM is percentage
            -SCALAR_ID is the same info as scalar factor
            -VECTOR is a unique way of describing the combination of
                region/age/sex/etc, already have this info elsewhere
            -SYMBOL and TERMINATED have no actual values
            -DECIMALS is 1 for every row
            """
            chunk.drop(["DGUID",
                        "UOM_ID",
                        "SCALAR_FACTOR",
                        "SCALAR_ID",
                        "VECTOR",
                        "SYMBOL",
                        "TERMINATED",
                        "DECIMALS"],
                       axis=1, inplace=True)

            # rename columns for SQL (no spaces, more descriptive, etc)
            chunk.rename(columns={"REF_DATE":"date",
                                  "GEO":"region",
                                  "Labour force characteristics":"labourcharacteristics",
                                  "Sex":"sex",
                                  "Age group":"agegroup",
                                  "Statistics":"statistic",
                                  "Data type":"datatype",
                                  "UOM":"unit",
                                  "COORDINATE":"coordinate",
                                  "VALUE":"value",
                                  "STATUS":"status"}, inplace=True)

            # start upload this chunk
            if details: print("Starting upload chunk:", i+1)

            # take chunk, append to sql table if it exists (or create, if it doesnt)
            chunk.to_sql(tablename,
                         engine,
                         index=False,
                         if_exists='append',
                         chunksize=25000,
                         method=None)

            # done this chunk
            if details: print("Finished upload chunk:", i+1)

    # when finished,
    if details: print(f"All {i+1} chunks uploaded to table '{tablename}'")
    return None

def upload_labour_pivot(tablenamein,
                        tablenameout,
                        whichsex = 'both sexes',
                        whichagegroup = '15 years and over',
                        whichstatistic = 'estimate',
                        whichdatatype = 'unadjusted'):

    """
    takes input table, pivots based on value of labourcharacteristics, reuploads to SQL
    """
    labourpivotquery = f"""
    SELECT
        CONCAT(SUBSTRING(date, 1, 7), ':', region) AS dateregion,
        date,
        region,
        MAX(CASE WHEN labourcharacteristics = 'Population' THEN value END) "Population_thousands",
        MAX(CASE WHEN labourcharacteristics = 'Labour force' THEN value END) "LabourForce_thousands",
        MAX(CASE WHEN labourcharacteristics = 'Employment' THEN value END) "Employment_thousands",
        MAX(CASE WHEN labourcharacteristics = 'Full-time employment' THEN value END) "Fulltime_thousands",
        MAX(CASE WHEN labourcharacteristics = 'Part-time employment' THEN value END) "Parttime_thousands",
        MAX(CASE WHEN labourcharacteristics = 'Unemployment' THEN value END) "Unemployment_thousands",
        MAX(CASE WHEN labourcharacteristics = 'Unemployment rate' THEN value END) "Unemployment_percent",
        MAX(CASE WHEN labourcharacteristics = 'Participation rate' THEN value END) "Participation_percent",
        MAX(CASE WHEN labourcharacteristics = 'Employment rate' THEN value END) "Employment_percent"
    FROM
        {tablenamein}
    WHERE (
            sex = '{whichsex}'
        AND agegroup = '{whichagegroup}'
        AND statistic = '{whichstatistic}'
        AND datatype = '{whichdatatype}')
    GROUP BY
        date,
        region
    ORDER BY
        date
    """

    # turn into df
    labourpivotdf = pdsql(labourpivotquery)

    # interpret as datetime format
    labourpivotdf['date'] = pd.to_datetime(labourpivotdf['date'])
    # new column quarter based on regionYYYYQQ
    labourpivotdf['quarter'] = labourpivotdf["region"] + labourpivotdf['date'].dt.to_period('Q').astype(str)

    # df to SQL table
    labourpivotdf.to_sql(tablenameout, engine, index=False, if_exists = "replace")

def multi_line_plot(
    df : pd.DataFrame,             # df for your data
    columntuples : list[tuple],    # list of tuples of column names [(xcol,ycol), ...]
    serieslabels : list[str],      # series labels ['series1', 'series2']
    xlabel : str = '',             # x axis label
    ylabel : str = '',             # y axis label
    title : str = '',              # title label
    figsize : tuple = (1100, 500), # (width, height) for figure (in pixels)
    titlesize : int = 30           # font size for title
) -> tuple:
    """
    Plot multiple columns on the same figure
    """
    # create figure object
    fig = go.Figure()

    # loop over tuples, add each to plot
    for i, coltuple in enumerate(columntuples):
        fig.add_trace(go.Scatter(
            x = df[coltuple[0]],
            y = df[coltuple[1]],
            name = serieslabels[i]
        ))

    # add labels
    fig.update_layout(
        title =title,
        xaxis_title = xlabel,
        yaxis_title = ylabel
    )

    # make it look nice
    fig.update_xaxes(ticks = 'outside')
    fig.update_yaxes(ticks = 'outside')

    fig.update_layout(title_x = 0.5, title = dict(font=dict(size=titlesize)), width=figsize[0], height=figsize[1])

    # show figure
    fig.show()
    return None

def custom_scatter(
    xdata : pd.DataFrame,
    ydata : pd.DataFrame,
    markersize : int = 7,
    colordata : list = None,
    cbartitle : str = '',
    colorscale : str = 'Viridis',
    titlelabel : str = '',
    xlabel : str = '',
    ylabel : str = '',
    titlesize : int = 30,
    plotwidth : int = 1100,
    plotheight : int = 500,
    xrange : list[int] = None,
    yrange : list[int] = None
) -> None:
    """
    custom plotly scatterplot function
    """
    # create figure object
    fig = go.Figure()

    # add data to figure objc
    fig.add_trace(go.Scatter(
        x = xdata,
        y = ydata,
        marker=dict(
            size=markersize,
            color=colordata,
            colorbar=dict(
                title=dict(text=cbartitle)
            ),
            colorscale=colorscale
        ),
        mode='markers'
    ))

    # add labels
    fig.update_layout(
        title =titlelabel,
        xaxis_title = xlabel,
        yaxis_title = ylabel,
        xaxis_range = xrange,
        yaxis_range = yrange
    )

    # make it look nice
    fig.update_xaxes(ticks = 'outside')
    fig.update_yaxes(ticks = 'outside')

    fig.update_layout(title_x = 0.5, title = dict(font=dict(size=titlesize)), width=plotwidth, height=plotheight)

    # show figure
    fig.show()
    return None

# choropleth plot function definition
def choroplot(
    dataframe : pd.DataFrame,                                        # your dataframe by province
    data_whichcolumn : str,                                          # column name you want to plot
    prov_filename = GEOJSONPATH,                                     # filename for geographic province boundaries
    prov_columns_keep = ['geo_point_2d','prov_name_en','geometry'],  # what columns in geo file to keep
    prov_regionnamecolumn = 'prov_name_en',                          # geo file 'province name' column
    data_regionnamecolumn = 'region',                                # your df 'province name' column
    figax : tuple = (None, None),                                    # pass in custom tuple of (fig, ax) objects for plotting onto
    plotsize = (10,8),                                               # figsize for matplotlib
    plottitlename = '',                                              # plot title text
    plotlegendname = '',                                             # plot legend text
    plotlegendorientation = 'vertical',                              # colorbar vertical or horizontal
    datacolormap = 'OrRd',                                           # colormap name
    vmin = None,                                                     # optional colormap minimum value
    vmax = None,                                                     # optional colormap maximal value
    missingcolor = 'lightgrey',                                      # NaN value color
    legendon = True,                                                 # Bool : legend on/off
    boundaryon = False,                                              # Province outlines on/off
    axisoff = True                                                   # plot axis' on/off (x and y axis are lat/long)
    ) -> None:                                                       # returns -> None
    """
    geo file from:
    Provinces and territories—Canada. (n.d.). Retrieved December 3, 2024, from https://data.opendatasoft.com/explore/dataset/georef-canada-province@public/information/?disjunctive.prov_name_en

    merges geo data and provided dataframe into one df, and then plot a choropleth of the specified dataframe column (uses matplotlib)

    """
    import geopandas as gpd

    # get geo bounds of provinces, throw out french names etc
    prov_data = gpd.read_file(prov_filename)
    prov_data = prov_data[prov_columns_keep]

    # merge provincial data and geographic data into one DF
    merged_geo = prov_data.merge(dataframe, how='left', left_on=prov_regionnamecolumn,right_on=data_regionnamecolumn).set_index(data_regionnamecolumn)

    # setup plot. If didn't provide a figure and axis, make one
    if figax == (None, None):
        fig, ax = plt.subplots(1,1, figsize=plotsize)
    else:
        # pull out figure and axis from figax input
        fig, ax = figax

    # province outline
    if boundaryon:
        merged_geo.boundary.plot(ax=ax, edgecolor="black")

    # df.plot(...parameters...)
    merged_geo.plot(
        column=data_whichcolumn, # which column to plot
        ax = ax, # axis object to plot onto
        missing_kwds={"color":missingcolor}, # missing values
        cmap = datacolormap, # colomap
        vmin = vmin,
        vmax = vmax,
        legend = legendon, # legend on or off
        legend_kwds = {'label':plotlegendname, 'orientation':plotlegendorientation}) # legend keywords

    ax.set_aspect(2) # aspect ratio
    if axisoff: ax.set_axis_off() # turn off x and y axis markers

    ax.set_title(plottitlename)

    return fig, ax

originialopiatedata = pd.read_csv(OPIATEPATH)

opiatedata = originialopiatedata[(originialopiatedata['Time_Period'] == "By quarter") & (originialopiatedata['Specific_Measure'] == "Overall numbers")].reset_index(drop=True)

opiatedata.drop(columns=["Source", "Specific_Measure", "PRUID", "Time_Period", "Aggregator", "Disaggregator", "Unit", "Substance"], inplace = True)

opiatedata["Region_Year_Quarter"] = opiatedata["Region"] + opiatedata["Year_Quarter"].str.replace(" ", "")

opiatedata["Value"] = opiatedata["Value"].replace({"Suppr.":np.nan, "n/a":np.nan})

opiatedata = opiatedata.pivot(index = ["Region", "Year_Quarter", "Region_Year_Quarter"], columns = "Type_Event", values = "Value").reset_index()

opiatedata = opiatedata.rename(columns = {"Accidental apparent opioid toxicity deaths":"Accidental_opioid_deaths",
                              "Accidental apparent stimulant toxicity deaths":"Accidental_stimulant_deaths",
                              "Accidental opioid-related poisoning ED visits":"Accidental_opioid_poisoning_ED_visits",
                              "Accidental opioid-related poisoning hospitalizations":"Accidental_opioid_poisoning_hospitalizations",
                              "Accidental stimulant-related poisoning ED visits":"Accidental_stimulant_poisoning_ED_visits",
                              "Accidental stimulant-related poisoning hospitalizations":"Accidental_stimulant_poisoning_hospitalizations",
                              "EMS responses to suspected opioid-related overdoses":"EMS_responses_to_opioid_overdoses",
                              "Total apparent opioid toxicity deaths":"Total_opioid_toxicity_deaths",
                              "Total apparent stimulant toxicity deaths":"Total_stimulant_toxicity_deaths",
                              "Total opioid-related poisoning ED visits":"Total_opioid_poisoning_ED_visits",
                              "Total opioid-related poisoning hospitalizations":"Total_opioid_poisoning_hospitalizations",
                              "Total stimulant-related poisoning ED visits":"Total_stimulant_poisoning_ED_visits",
                              "Total stimulant-related poisoning hospitalizations":"Total_stimulant_poisoning_hospitalizations"})

opiatedata.Accidental_opioid_deaths = opiatedata.Accidental_opioid_deaths.astype('float')
opiatedata.Accidental_stimulant_deaths = opiatedata.Accidental_stimulant_deaths.astype('float')
opiatedata.Accidental_opioid_poisoning_ED_visits = opiatedata.Accidental_opioid_poisoning_ED_visits.astype('float')
opiatedata.Accidental_opioid_poisoning_hospitalizations = opiatedata.Accidental_opioid_poisoning_hospitalizations.astype('float')
opiatedata.Accidental_stimulant_poisoning_ED_visits = opiatedata.Accidental_stimulant_poisoning_ED_visits.astype('float')
opiatedata.Accidental_stimulant_poisoning_hospitalizations = opiatedata.Accidental_stimulant_poisoning_hospitalizations.astype('float')
opiatedata.EMS_responses_to_opioid_overdoses = opiatedata.EMS_responses_to_opioid_overdoses.astype('float')
opiatedata.Total_opioid_toxicity_deaths = opiatedata.Total_opioid_toxicity_deaths.astype('float')
opiatedata.Total_stimulant_toxicity_deaths = opiatedata.Total_stimulant_toxicity_deaths.astype('float')
opiatedata.Total_opioid_poisoning_ED_visits = opiatedata.Total_opioid_poisoning_ED_visits.astype('float')
opiatedata.Total_opioid_poisoning_hospitalizations = opiatedata.Total_opioid_poisoning_hospitalizations.astype('float')
opiatedata.Total_stimulant_poisoning_ED_visits = opiatedata.Total_stimulant_poisoning_ED_visits.astype('float')
opiatedata.Total_stimulant_poisoning_hospitalizations = opiatedata.Total_stimulant_poisoning_hospitalizations.astype('float')

print(opiatedata["Region_Year_Quarter"].nunique())
print(opiatedata["Region_Year_Quarter"].count())

611
611

opiatedata.to_sql("Opiates", if_exists='replace', con=engine, method=None, index=False)

611

alldataprovinces = SQL("""
    select *
    from Opiates
    where region != 'Territories'
        and region != 'Whitehorse, Yukon'
        and region != 'Winnipeg, Manitoba'
        and region != 'Yellowknife, Northwest Territories'
        and region != 'Northern and rural Manitoba'
        and region != 'Canada'
    """)

alldataprovinces

mostdeathsperquarter = SQL("""
    select year_quarter,
    cast(sum(Total_opioid_toxicity_deaths) as int) as Total_Deaths
    from Opiates
    where region != 'Territories'
        and region != 'Whitehorse, Yukon'
        and region != 'Winnipeg, Manitoba'
        and region != 'Yellowknife, Northwest Territories'
        and region != 'Northern and rural Manitoba'
        and region != 'Canada'
    group by year_quarter
    order by Total_Deaths desc
    limit 20""")

mostdeathsperquarter

totaldeathsperprovince = SQL("""
    select Region,
    cast(sum(Total_opioid_toxicity_deaths) as int) as Total_Opioid_Deaths
    from Opiates
    where region != 'Territories'
        and region != 'Whitehorse, Yukon'
        and region != 'Winnipeg, Manitoba'
        and region != 'Yellowknife, Northwest Territories'
        and region != 'Northern and rural Manitoba'
        and region != 'Canada'
    group by region
    order by Total_Opioid_Deaths desc
    """)

totaldeathsperprovince

minandmaxperprovince = SQL("""
    select region,
    cast(max(Total_opioid_toxicity_deaths) as int) as Max_Deaths_Quarter,
    cast(min(Total_opioid_toxicity_deaths) as int) as Min_Deaths_Quarter
    from Opiates
    where region != 'Territories'
        and region != 'Whitehorse, Yukon'
        and region != 'Winnipeg, Manitoba'
        and region != 'Yellowknife, Northwest Territories'
        and region != 'Northern and rural Manitoba'
        and region != 'Canada'
    group by region
    order by Max_Deaths_Quarter desc, Min_Deaths_Quarter asc
    """)

minandmaxperprovince

morethanaveragequarters = SQL("""
    select Region_Year_Quarter,
    cast(Total_opioid_toxicity_deaths as int) as Total_opioid_toxicity_deaths
    from Opiates
    where region != 'Territories'
        and region != 'Whitehorse, Yukon'
        and region != 'Winnipeg, Manitoba'
        and region != 'Yellowknife, Northwest Territories'
        and region != 'Northern and rural Manitoba'
        and region != 'Canada'
        and Total_opioid_toxicity_deaths > (select avg(Total_opioid_toxicity_deaths) from Opiates
                                            where region != 'Territories'
                                            and region != 'Whitehorse, Yukon'
                                            and region != 'Winnipeg, Manitoba'
                                            and region != 'Yellowknife, Northwest Territories'
                                            and region != 'Northern and rural Manitoba'
                                            and region != 'Canada')
    group by Total_opioid_toxicity_deaths desc
    """)

morethanaveragequarters

intentionalopioiddeaths = SQL("""
    select concat(region,' has a total of ', sum(Total_opioid_toxicity_deaths) - sum(Accidental_opioid_deaths) ,' suspected intentional opioid overdoses') as 'Suspected intentional opioid overdoses'
    from Opiates
    where Total_opioid_toxicity_deaths > 0
    and Accidental_opioid_deaths > 0
    group by region
    """)

intentionalopioiddeaths

#reading in data, and changing column names
prices_df = pd.read_csv(CPIPATH)
prices_df.rename(columns={"Products and product groups":"product", 'TERMINATED': 'term'}, inplace=True)

#splitting data into chunks to add to an initial table
prices_df_first = prices_df.iloc[1:46864]
prices_df_mid = prices_df.iloc[46864:93725]
prices_df_last = prices_df.iloc[93725:140593]

# adding CPI to CPI initial
prices_df_first.to_sql('CPI_initial', engine, index=False,if_exists='replace')
prices_df_mid.to_sql('CPI_initial', engine, index=False,if_exists='append')
prices_df_last.to_sql('CPI_initial', engine, index=False,if_exists='append')

46867

#Dropping unnecessary columns
with engine.connect() as connection:
    connection.execute(text("ALTER TABLE CPI_initial DROP COLUMN STATUS;"))

with engine.connect() as connection:
    connection.execute(text("ALTER TABLE CPI_initial DROP COLUMN SYMBOL;"))

with engine.connect() as connection:
    connection.execute(text("ALTER TABLE CPI_initial DROP COLUMN term;"))

explore_data = pd.read_sql_query("SELECT * FROM CPI_initial LIMIT 10", engine)
explore_data

#Consulted source:
#https://dba.stackexchange.com/questions/164711/how-to-pivot-rows-into-columns-mysql

pivoteddf = pd.read_sql_query("""
SELECT
    CONCAT(REF_DATE, ':', GEO) AS `year_month_region`,
    MAX(CASE WHEN product = 'Food purchased from stores' THEN VALUE END) AS "Food_purchased_from_stores",
    MAX(CASE WHEN product = 'Food purchased from restaurants' THEN VALUE END) AS "Food__purchased_from_restaurants",
    MAX(CASE WHEN product = 'Rented accommodation' THEN VALUE END) AS "Rented_accommodation",
    MAX(CASE WHEN product = 'Owned accommodation' THEN VALUE END) AS "Owned_accommodation",
    MAX(CASE WHEN product = 'Water, fuel and electricity' THEN VALUE END) AS "Water_fuel_and_electricity",
    MAX(CASE WHEN product = 'Household operations' THEN VALUE END) AS "Household_operations",
    MAX(CASE WHEN product = 'Household furnishings and equipment' THEN VALUE END) AS "Household_furnishings_and_equipment",
    MAX(CASE WHEN product = 'Clothing' THEN VALUE END) AS "Clothing",
    MAX(CASE WHEN product = 'Footwear' THEN VALUE END) AS "Footwear",
    MAX(CASE WHEN product = 'Clothing accessories, watches and jewellery' THEN VALUE END) AS "Clothing_accessories_watches_jewellery",
    MAX(CASE WHEN product = 'Clothing material, notions and services' THEN VALUE END) AS "Clothing_material_notions_services",
    MAX(CASE WHEN product = 'Public transportation' THEN VALUE END) AS "Public_transportation",
    MAX(CASE WHEN product = 'Gasoline' THEN VALUE END) AS "Gasoline",
    MAX(CASE WHEN product = 'Private transportation' THEN VALUE END) AS "Private_transportation",
    MAX(CASE WHEN product = 'Health care' THEN VALUE END) AS "Health_care",
    MAX(CASE WHEN product = 'Personal care' THEN VALUE END) AS "Personal_care",
    MAX(CASE WHEN product = 'Recreation' THEN VALUE END) AS "Recreation",
    MAX(CASE WHEN product = 'Education and reading' THEN VALUE END) AS "Education_and_reading",
    MAX(CASE WHEN product = 'Alcoholic beverages' THEN VALUE END) AS "Alcoholic_beverages",
    MAX(CASE WHEN product = 'Tobacco products and smokers'' supplies' THEN VALUE END) AS "Tobacco_smokers_supplies",
    MAX(CASE WHEN product = 'Recreational cannabis (201812=100)' THEN VALUE END) AS "Recreational_cannabis",
    MAX(CASE WHEN product = 'Goods' THEN VALUE END) AS "Goods",
    MAX(CASE WHEN product = 'Services' THEN VALUE END) AS "Services"
FROM CPI_initial
GROUP BY `year_month_region`
ORDER BY `year_month_region`;
""", engine)

#creating two new columns by splitting up the key
pivoteddf[['Year', 'Province']] = pivoteddf['year_month_region'].str.split(':', expand = True)

pivoteddf['Year'] = pd.to_datetime(pivoteddf['Year'])
pivoteddf['Quarter'] = pivoteddf['Year'].dt.to_period('Q')
pivoteddf['Quarter'] = pivoteddf['Quarter'].astype(str)
pivoteddf['Region_Year_Quarter'] = pivoteddf['Province'] + pivoteddf['Quarter']

pivoteddf.to_sql('CPI_table_quarter', engine, index=False,if_exists='replace')

6540

#getting CPI values for alcohol price for 1979
query_CPI_1 = pd.read_sql_query(
    "SELECT REF_DATE, product, GEO, AVG(VALUE) AS average_value "
    "FROM CPI_initial "
    "WHERE product = 'Alcoholic beverages' AND REF_DATE LIKE '%1979%'"
    "GROUP BY GEO;",
    engine
)

#getting CPI values for alcohol price for 2002
query_CPI_2 = pd.read_sql_query(
"""SELECT REF_DATE, product, GEO, AVG(VALUE) AS average_value
FROM CPI_initial
WHERE product = 'Alcoholic beverages' AND REF_DATE LIKE '%2002%'
GROUP BY GEO;""", engine
)


#getting CPI values for alcohol price for 2023
query_CPI_3 = pd.read_sql_query(
"""SELECT REF_DATE, product, GEO, AVG(VALUE) AS average_value
FROM CPI_initial
WHERE product = 'Alcoholic beverages' AND REF_DATE LIKE '%2023%'
GROUP BY GEO;""", engine
)

#creating a grouped bar chart for the queries
fig2 = go.Figure(
    data=[
        go.Bar(
            name="1979",
            x=query_CPI_1["GEO"],
            y=query_CPI_1["average_value"],
            offsetgroup=0,
        ),
        go.Bar(
            name="2002",
            x=query_CPI_2["GEO"],
            y=query_CPI_2["average_value"],
            offsetgroup=1,
        ),

        go.Bar(
            name="2023",
            x=query_CPI_3["GEO"],
            y=query_CPI_3["average_value"],
            offsetgroup=2,
        ),
    ],
    layout=go.Layout(
        title="Average Price of Alcohol By Province With 2002 as Baseline",
        yaxis_title="Average Alcohol Price(2002=100)"
    )
)
fig2.show()

query_CPI_4 = pd.read_sql_query("""
SELECT REF_DATE, GEO, product, avg(VALUE) as avg_price
FROM CPI_initial
WHERE REF_DATE LIKE '%2023%' AND product = 'Food purchased from stores'
GROUP BY GEO, product HAVING avg(VALUE) IS NOT NULL;""", engine)

query_CPI_5 = pd.read_sql_query("""
SELECT REF_DATE, GEO, product, avg(VALUE) as avg_price
FROM CPI_initial
WHERE REF_DATE LIKE '%2023%' AND product = 'Food purchased from restaurants'
GROUP BY GEO, product HAVING avg(VALUE) IS NOT NULL;""", engine)

fig = go.Figure(
    data=[
        go.Bar(
            name="Food Purchased From Stores",
            x=query_CPI_4["GEO"],
            y=query_CPI_4["avg_price"],
            offsetgroup=0,
        ),
        go.Bar(
            name="Food Purchased From Restaurants",
            x=query_CPI_5["GEO"],
            y=query_CPI_5["avg_price"],
            offsetgroup=1,
        ),
    ],
    layout=go.Layout(
        title="Cost of Food Across Provinces",
        yaxis_title="Average Price(2002=100)"
    )
)
fig.show()

query_CPI_6 = pd.read_sql_query("""SELECT avg(VALUE) AS average_value, REF_DATE, product
FROM CPI_initial
WHERE product IN ('Rented accommodation', 'Owned accommodation', 'Water, fuel and electricity', 'Household operations', 'Household furnishings and equipment')
GROUP BY REF_DATE, product HAVING avg(VALUE) IS NOT NULL;""", engine)


fig = px.line(query_CPI_6, x= query_CPI_6['REF_DATE'], y= query_CPI_6['average_value'], color = 'product', title = 'Average Price of Housing From 1979-2024')
fig.show()

query_CPI_7 = pd.read_sql_query("""
SELECT avg(VALUE) AS average_value, REF_DATE, GEO, product
FROM CPI_initial
WHERE GEO = 'Alberta' AND  product IN ('Private transportation', 'Gasoline', 'Public transportation')
GROUP BY GEO, REF_DATE, product
HAVING avg(VALUE) IS NOT NULL;""", engine)


fig = px.line(query_CPI_7, x= query_CPI_7['REF_DATE'], y= query_CPI_7['average_value'], color = 'product', title = 'Average Price of Transportation in Alberta From 1979 to 2024')
fig.show()

query_CPI_7 = pd.read_sql_query("SELECT*FROM CPI_table_quarter",engine)
fig = make_subplots(rows=6, cols=4, subplot_titles=("Food Purchased From Stores",
                                                    "Food purchased from restaurants",
                                                    "Rented accommodation",
                                                    "Owned accommodation",
                                                    'Water, fuel and electricity',
                                                    'Household operations',
                                                    'Household furnishings and equipment',
                                                    'Clothing',
                                                    'Footwear',
                                                    'Clothing accessories, watches and jewellery',
                                                    'Clothing material, notions and services',
                                                    'Public transportation',
                                                    'Gasoline',
                                                    'Private transportation',
                                                    'Health care',
                                                    'Personal care',
                                                    'Recreation',
                                                    'Education and reading' ,
                                                    'Alcoholic beverages',
                                                    'Tobacco products and smokers supplies',
                                                    'Recreational cannabis',
                                                    'Goods',
                                                    'Services'
                                                   ))


fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Food_purchased_from_stores"], name="Food_purchased_from_stores"),
   row=1, col=1
)

fig.update_yaxes(title_text="Health Care Costs", row=1, col=1)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Food__purchased_from_restaurants"], name="Food__purchased_from_restaurants"),
    row=1, col=2
)

fig.update_yaxes(title_text="Health Care Costs", row=1, col=2)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Rented_accommodation"], name="Rented_accommodation"),
    row=1, col=3
)

fig.update_yaxes(title_text="Health Care Costs", row=1, col=3)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Owned_accommodation"], name="Owned_accommodation"),
    row=1, col=4
)

fig.update_yaxes(title_text="Health Care Costs", row=1, col=4)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Water_fuel_and_electricity"], name="Water_fuel_and_electricity"),
    row=2, col=1
)

fig.update_yaxes(title_text="Health Care Costs", row=2, col=1)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7[ "Household_operations"], name= "Household_operations"),
    row=2, col=2
)

fig.update_yaxes(title_text="Health Care Costs", row=2, col=2)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Household_furnishings_and_equipment"], name="Household_furnishings_and_equipment"),
    row=2, col=3
)

fig.update_yaxes(title_text="Health Care Costs", row=2, col=3)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Clothing"], name="Clothing"),
    row=2, col=4
)

fig.update_yaxes(title_text="Health Care Costs", row=2, col=4)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Footwear"], name="Footwear"),
    row=3, col=1
)

fig.update_yaxes(title_text="Health Care Costs", row=3, col=1)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Clothing_accessories_watches_jewellery"], name="Clothing_accessories_watches_jewellery"),
    row=3, col=2
)


fig.update_yaxes(title_text="Health Care Costs", row=3, col=2)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Clothing_material_notions_services"], name="Clothing_material_notions_services"),
    row=3, col=3
)

fig.update_yaxes(title_text="Health Care Costs", row=3, col=3)


fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Public_transportation"], name="Public_transportation"),
    row=3, col=4
)

fig.update_yaxes(title_text="Health Care Costs", row=3, col=4)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Gasoline"], name="Gasoline"),
    row=4, col=1
)


fig.update_yaxes(title_text="Health Care Costs", row=4, col=1)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Private_transportation"], name="Private_transportation"),
    row=4, col=2
)


fig.update_yaxes(title_text="Health Care Costs", row=4, col=2)


fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7[ "Health_care"], name= "Health_care"),
    row=4, col=3
)

fig.update_yaxes(title_text="Health Care Costs", row=4, col=3)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Personal_care"], name= "Personal_care"),
    row=4, col=4
)

fig.update_yaxes(title_text="Health Care Costs", row=4, col=4)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Recreation"], name= "Recreation"),
    row=5, col=1
)

fig.update_yaxes(title_text="Health Care Costs", row=5, col=1)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7[ "Education_and_reading"], name=  "Education_and_reading"),
    row=5, col=2
)

fig.update_yaxes(title_text="Health Care Costs", row=5, col=2)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Alcoholic_beverages"], name="Alcoholic_beverages"),
    row=5, col=3
)

fig.update_yaxes(title_text="Health Care Costs", row=5, col=3)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Tobacco_smokers_supplies"], name="Tobacco_smokers_supplies"),
    row=5, col=4
)

fig.update_yaxes(title_text="Health Care Costs", row=5, col=4)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Recreational_cannabis"], name="Recreational_cannabis"),
    row=6, col=1
)

fig.update_yaxes(title_text="Health Care Costs", row=6, col=1)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Goods"], name="Goods"),
    row=6, col=2
)

fig.update_yaxes(title_text="Health Care Costs", row=6, col=2)

fig.add_trace(
    go.Box(x=query_CPI_7["Province"], y=query_CPI_7["Services"], name="Services"),
    row=6, col=3
)

fig.update_xaxes(title_text="Health Care Costs", row=6, col=3)

fig.update_layout(title="CPI Comparison by Product From 1978-2024 Across All Provinces",showlegend=False, height = 3000, width = 1500)

fig.show()

query_CPI_8 = pd.read_sql_query("""
    SELECT
        Year,
        Province,
        AVG(Health_care) AS Health_care
    FROM
        CPI_table_quarter
    WHERE
        Year BETWEEN "1978-09-01" AND "2024-01-01"
    GROUP BY
        Province, Year
""", engine)

fig = px.line(query_CPI_8, x= query_CPI_8['Year'], y= query_CPI_8['Health_care'], color = 'Province', title = 'Average Price of Health Care By Province and Year For 1978-2024')
fig.show()

# set True to drop and re-upload the data & pivot table to the SQL database
reuploadLABOUR = True

if reuploadLABOUR:
    uploadLABOURdatabychunk(filename=LABOURPATH, tablename = "labour_initial")
    upload_labour_pivot("labour_initial", "labour_table")

table "labour_initial" dropped (or doesnt exist)
Starting upload chunk: 1
Finished upload chunk: 1
Starting upload chunk: 2
Finished upload chunk: 2
Starting upload chunk: 3
Finished upload chunk: 3
Starting upload chunk: 4
Finished upload chunk: 4
Starting upload chunk: 5
Finished upload chunk: 5
Starting upload chunk: 6
Finished upload chunk: 6
All 6 chunks uploaded to table 'labour_initial'

# pivot table head
display(
    pdsql("SELECT * FROM labour_table LIMIT 5")
)

# SQL Query -> average unemployment over the last 47 years
labouravg = pdsql("""
SELECT
    region,
    AVG(value) as AvgUnemployment
FROM labour_initial
WHERE
    statistic = 'Estimate'
    AND datatype = 'Unadjusted'
    AND sex = 'Both sexes'
    AND agegroup = '15 years and over'
    AND labourcharacteristics = 'Unemployment rate'
GROUP BY region
ORDER BY AvgUnemployment
""")

display(labouravg)

# plot average unemployment over the last 47 years
fig, ax = choroplot(
    dataframe = labouravg,
    data_whichcolumn = 'AvgUnemployment',
    prov_filename = GEOJSONPATH,
    data_regionnamecolumn = 'region',
    plotsize = (10,8),
    plottitlename = 'Unemployment Across Canada (1977-2024 Average)',
    plotlegendname = 'Unemployment (%)',
    plotlegendorientation = 'vertical',
    datacolormap = 'OrRd',
    vmin = 0,
    missingcolor = 'lightgrey',
    legendon = True,
    boundaryon = True,
    axisoff = True)

fig.show()

# SQL Query -> best year for employment in each province
bestyearlabour = pdsql("""
SELECT a.region AS Region, a.year AS Year, a.yearavg AS AverageEmploymentRate
FROM (SELECT b.region, b.yearavg, b.year, b.labourcharacteristics, ROW_NUMBER() OVER (PARTITION BY b.region ORDER BY b.yearavg DESC) ranked_order
                FROM (
                        SELECT region, AVG(value) as yearavg, SUBSTRING(date, 1, 4) AS year, labourcharacteristics
                        FROM labour_initial
                        WHERE labourcharacteristics = 'employment rate'
                                AND sex = 'both sexes'
                                AND agegroup = '15 years and over'
                                AND statistic = 'estimate'
                                AND datatype = 'unadjusted'
                        GROUP BY region,year) as b
    ) AS a
WHERE a.ranked_order = 1
ORDER BY a.yearavg
""")

display(bestyearlabour)

# SQL Query -> Employment rate by gender, by decade
genderdecadelabour = pdsql("""
SELECT
    CONCAT(SUBSTRING(date, 1, 3), "", "0") AS decade,
    AVG(CASE WHEN sex = 'Males' THEN value END) as AvgMale,
    AVG(CASE WHEN sex = 'Females' THEN value END) as AvgFemale,
    AVG(CASE WHEN sex = 'Both Sexes' THEN value END) as AvgBoth,
    AVG(CASE WHEN sex = 'Males' THEN value END) - AVG(CASE WHEN sex = 'Females' THEN value END) AS Difference
FROM labour_initial
WHERE region = 'Canada'
    AND labourcharacteristics = 'Employment rate'
    AND agegroup = '25 years and over'
    AND statistic = 'estimate'
    AND datatype = 'unadjusted'
GROUP BY decade
ORDER BY Difference DESC
""")

# Plot: Employment rates by gender by decade
multi_line_plot(
    df = genderdecadelabour,
    columntuples = [('decade', 'AvgMale'), ('decade', 'AvgFemale'), ('decade', 'AvgBoth')],
    serieslabels = ['Average Male', 'Average Female', 'Average Both'],
    xlabel = 'Decade',
    ylabel = 'Employment Rate (%)',
    title = 'Gender Employment Gap in Canada by Decade',
    figsize = (1100, 500),
    titlesize = 30
)

# SQL Query -> above average participation dropoff 2019-2020, by province
labourcovid = pdsql("""
SELECT *
    FROM (
        SELECT
            region,
            AVG(CASE WHEN date LIKE '2019%' THEN value END) AS avg2019,
            AVG(CASE WHEN date LIKE '2020%' THEN value END) AS avg2020,
            AVG(CASE WHEN date LIKE '2019%' THEN value END) - AVG(CASE WHEN date LIKE '2020%' THEN value END) as diff
        FROM labour_initial
        WHERE labourcharacteristics = 'participation rate'
            AND sex = 'both sexes'
            AND agegroup = '15 years and over'
            AND statistic = 'estimate'
            AND datatype = 'unadjusted'
        GROUP BY region ) as t1
WHERE t1.diff > (SELECT AVG(diff) FROM (
            SELECT
                region,
                AVG(CASE WHEN date LIKE '2019%' THEN value END) - AVG(CASE WHEN date LIKE '2020%' THEN value END) as diff
            FROM labour_initial
            WHERE labourcharacteristics = 'participation rate'
                AND sex = 'both sexes'
                AND agegroup = '15 years and over'
                AND statistic = 'estimate'
                AND datatype = 'unadjusted'
            GROUP BY region) AS t2
        )
ORDER BY diff DESC;
""")

display(labourcovid)

# read csv to a dataframe
deaths_df = pd.read_csv(DEATHSPATH)
deaths_df.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 108823 entries, 0 to 108822
Data columns (total 16 columns):
 #   Column           Non-Null Count   Dtype  
---  ------           --------------   -----  
 0   REF_DATE         108823 non-null  object 
 1   GEO              108823 non-null  object 
 2   DGUID            108823 non-null  object 
 3   Cause of death   108823 non-null  object 
 4   Characteristics  108823 non-null  object 
 5   UOM              108823 non-null  object 
 6   UOM_ID           108823 non-null  int64  
 7   SCALAR_FACTOR    108823 non-null  object 
 8   SCALAR_ID        108823 non-null  int64  
 9   VECTOR           108823 non-null  object 
 10  COORDINATE       108823 non-null  object 
 11  VALUE            97850 non-null   float64
 12  STATUS           10973 non-null   object 
 13  SYMBOL           0 non-null       float64
 14  TERMINATED       0 non-null       float64
 15  DECIMALS         108823 non-null  int64  
dtypes: float64(3), int64(3), object(10)
memory usage: 13.3+ MB

# output the first 5 rows
deaths_df.head()

deaths_df.drop(columns = ['DGUID', 'Characteristics','UOM','UOM_ID','SCALAR_FACTOR',
                           'SCALAR_FACTOR','SCALAR_ID','VECTOR','COORDINATE','STATUS',
                           'SYMBOL','TERMINATED','DECIMALS'], inplace = True)

deaths_df.rename(columns = {'REF_DATE': 'Date',
                            'GEO': 'Region',
                            'Cause of death' : 'Cause_of_Death',
                            'VALUE' : 'Number_of_Deaths'}, inplace=True)

# add cloumn and change Date from object to datetime and change to quarterly
deaths_df['Quarterly'] = pd.to_datetime(deaths_df['Date']).dt.to_period('Q').astype(str)

# change Date from object to datetime and change to monthly
deaths_df['Monthly'] = pd.to_datetime(deaths_df['Date']).dt.to_period('M').astype(str)

# strip Province of ", place of occurrence" and excess whitespace
deaths_df['Region'] = deaths_df['Region'].str.replace(', place of occurrence', '', regex=True).str.strip()

# strip Cause_of_Death of "[]" and excess whitespace
deaths_df['Cause_of_Death'] = deaths_df['Cause_of_Death'].str.replace('\[.*?\]', '', regex=True).str.strip()

# add column to create unique key for quarterly
deaths_df['RegionYearQuarter'] = deaths_df['Region'].astype(str) + deaths_df['Quarterly'].astype(str)

# add column to create unique key for monthly
deaths_df['YearMonthRegion'] = deaths_df['Monthly'].astype(str) + str(":") + deaths_df['Region'].astype(str)

# import data into SQL dataframe, if it already exists replace it
# import in chunks to aviod crashing
deaths_df.to_sql('deaths', engine, if_exists='replace', index=None,
                 index_label=None, chunksize=1000, dtype=None, method=None)

108823

# drop date column
deaths_df.drop(columns=['Date'], inplace=True)

deaths_df.head()

quarterly_df = deaths_df.groupby(['RegionYearQuarter','Cause_of_Death'])['Number_of_Deaths'].sum().reset_index()
quarterly_df.head()

death_table_quarter = quarterly_df.pivot(index='RegionYearQuarter', columns='Cause_of_Death', values='Number_of_Deaths')

death_table_quarter.rename(columns = {
                                "Alzheimer's disease": "Alzheimer's_Disease",
                                "Cerebrovascular diseases" : "Cerebrovascular_Diseases",
                                "Chronic lower respiratory diseases" : "Chronic_Lower_Respiratory_Diseases",
                                "Malignant neoplasms":"Malignant_Neoplasms",
                                "Diabetes mellitus":"Diabetes_Mellitus",
                                "Diseases of heart":"Diseases_of_Heart",
                                "Influenza and pneumonia":"Influenza_and_Pneumonia",
                                "Intentional self-harm (suicide)":"Suicide",
                                "Nephritis, nephrotic syndrome and nephrosis":"Nephritis,_Nephrotic_Syndrome_and_Nephrosis",
                                "Accidents (unintentional injuries)": "Accidents_(Unintentional_Injuries)"},inplace=True)

death_table_quarter.head()

monthly_df = deaths_df.groupby(['YearMonthRegion','Cause_of_Death'])['Number_of_Deaths'].sum().reset_index()
monthly_df.head()

death_table_month = monthly_df.pivot(index='YearMonthRegion', columns='Cause_of_Death', values='Number_of_Deaths')

death_table_month.rename(columns = {
                                "Alzheimers disease": "Alzheimer's_Disease",
                                "Cerebrovascular diseases" : "Cerebrovascular_Diseases",
                                "Chronic lower respiratory diseases" : "Chronic_Lower_Respiratory_Diseases",
                                "Malignant neoplasms":"Malignant_Neoplasms",
                                "Diabetes mellitus":"Diabetes_Mellitus",
                                "Diseases of heart":"Diseases_of_Heart",
                                "Influenza and pneumonia":"Influenza_and_Pneumonia",
                                "Intentional self-harm (suicide)":"Suicide",
                                "Nephritis, nephrotic syndrome and nephrosis":"Nephritis,_Nephrotic_Syndrome_and_Nephrosis",
                                "Accidents (unintentional injuries)": "Accidents_(Unintentional_Injuries)"},inplace=True)

death_table_month.head()

# upload table into SQL
death_table_quarter.to_sql("death_table_quarter", engine, if_exists = "replace")

767

# upload table into SQL
death_table_month.to_sql("death_table_month", engine, if_exists = "replace")

2288

# create query to find max, min, average deaths for each cause per week
summary_query = pd.read_sql_query("""SELECT Cause_of_Death, AVG(Number_of_Deaths),
                                MAX(Number_of_Deaths), MIN(Number_of_Deaths)
                                FROM deaths GROUP BY Cause_of_Death""", engine)
print(summary_query)

                                 Cause_of_Death  AVG(Number_of_Deaths)  \
0            Accidents (unintentional injuries)              21.973255   
1                           Alzheimer's disease               9.289776   
2                      Cerebrovascular diseases              20.627567   
3            Chronic lower respiratory diseases              18.336843   
4                                      COVID-19              20.692708   
5                             Diabetes mellitus              10.770770   
6                             Diseases of heart              79.650416   
7                       Influenza and pneumonia               9.714647   
8               Intentional self-harm (suicide)               6.226703   
9                           Malignant neoplasms             118.979151   
10  Nephritis, nephrotic syndrome and nephrosis               5.419606   

    MAX(Number_of_Deaths)  MIN(Number_of_Deaths)  
0                   315.0                    0.0  
1                    85.0                    0.0  
2                   135.0                    0.0  
3                   140.0                    0.0  
4                   850.0                    0.0  
5                    95.0                    0.0  
6                   565.0                    0.0  
7                   220.0                    0.0  
8                    45.0                    0.0  
9                   670.0                    0.0  
10                   45.0                    0.0

# create query to find cause of death and number of deaths per week
deaths_query = pd.read_sql_query("""SELECT Cause_of_Death, Number_of_Deaths
                                    FROM deaths""", engine)

fig = px.box(deaths_query,
             x='Cause_of_Death',
             y='Number_of_Deaths',
             title='Number of Deaths for each Cause of Death per Week in Canada from 2010-2024',
             labels={'Cause_of_Death': 'Cause of Death', 'Number_of_Deaths': 'Number of Deaths'},
             width = 1000,
             height = 700)
fig.show()

# create query to find total number of deaths for each cause in Alberta in 2020
total_query = pd.read_sql_query("""SELECT Cause_of_Death,
            SUM(Number_of_Deaths) FROM deaths WHERE Region = 'Alberta'
            AND DATE_FORMAT(Date, '%Y') = '2020' GROUP BY Cause_of_Death
            ORDER BY SUM(Number_of_Deaths) DESC""", engine)

fig = px.bar(total_query,
                    x='Cause_of_Death',
                    y='SUM(Number_of_Deaths)',
                    title='Total Number of Deaths for each Cause of Death in Alberta in 2020',
                    labels={'Cause_of_Death': 'Cause of Death', 'SUM(Number_of_Deaths)': 'Total Number of Deaths'},
                    width = 1000,
                    height = 700)
fig.show()

# create query to find the total number of deaths for each cause for each year
causes_query = pd.read_sql_query("""SELECT Cause_of_Death,
            SUM(Number_of_Deaths), DATE_FORMAT(Date, '%Y') AS Year
            FROM deaths GROUP BY Cause_of_Death, Year""", engine)

fig = px.line(causes_query,
                    x='Year',
                    y='SUM(Number_of_Deaths)',
                    color='Cause_of_Death',
                    title='Number of Deaths from Each Cause per Year in Canada from 2010-2024',
                    labels={'Year': 'Year', 'SUM(Number_of_Deaths)': 'Total Number of Deaths'},
                    width = 1300,
                    height = 700)
fig.show()

# create query to find the total number of deaths caused by suicide in Canada in 2020
suicide_query = pd.read_sql_query("""SELECT Region, SUM(Number_of_Deaths)
            FROM deaths WHERE Cause_of_Death = 'Intentional self-harm (suicide)'
            AND DATE_FORMAT(Date, '%Y') = '2020' GROUP BY Region""", engine)

fig = px.bar(suicide_query,
                    x='Region',
                    y='SUM(Number_of_Deaths)',
                    title='Total Number of Deaths Caused by Suicide in Canada in 2020',
                    labels={'Region': 'Region', 'SUM(Number_of_Deaths)': 'Total Number of Deaths'},
                    width = 1000,
                    height = 700)
fig.show()

correlationdata = SQL("""select *
                        from Opiates

                        join
                        (select Region_Year_Quarter,
                        avg(Rented_accommodation) Rented_accommodation,
                        avg(Owned_accommodation) Owned_accommodation
                        from CPI_table_quarter
                        group by Region_Year_Quarter) as CPIavg
                        on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter """)

allcordata = correlationdata.drop(columns = ["Year_Quarter","Region_Year_Quarter","Accidental_stimulant_deaths","Accidental_stimulant_poisoning_ED_visits","Accidental_stimulant_poisoning_hospitalizations","Total_stimulant_poisoning_ED_visits"], inplace=True)
allcordata = correlationdata.filter(['Region','Total_opioid_toxicity_deaths', 'Rented_accommodation','Owned_accommodation'])

cancordata = allcordata.drop(columns = 'Region')

opfig = px.imshow(cancordata.corr(), color_continuous_scale='PuBu')
opfig.show()

overalltotals = SQL("""select Region,
                        Year_Quarter,
                        Opiates.Region_Year_Quarter,
                        avg(Owned_accommodation) Owned_accommodation,
                        avg(Rented_accommodation) Rented_accommodation,
                        (avg(Rented_accommodation)+avg(Owned_accommodation))/2 Combined_accommodation,
                        avg(Health_care) as Health_care,
                        sum(Total_opioid_toxicity_deaths) as Total_opioid_toxicity_deaths
                        from Opiates

                        join
                        (select Region_Year_Quarter,
                        avg(Rented_accommodation) Rented_accommodation,
                        avg(Owned_accommodation) Owned_accommodation,
                        avg(Health_care) Health_care
                        from CPI_table_quarter
                        group by Region_Year_Quarter) as CPIavg
                        on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                        where region != 'Whitehorse, Yukon'
                        and region != 'Yellowknife, Northwest Territories'
                        group by Year_Quarter""")

opfig1 = px.scatter(overalltotals, x= ['Owned_accommodation', 'Rented_accommodation'], y = 'Total_opioid_toxicity_deaths',
                    trendline = 'ols',
                    labels = {'Total_opioid_toxicity_deaths':'<b>Total Opioid Deaths<b>', 'Owned_accommodation':'<b>Owned Accomodation CPI<b>', 'variable':'Metric', 'value':'<b>Accommodation CPI<b>'},
                    title = '<b>Total Opioid Deaths as a Function of Accomodation CPI<b>',
                    width =1100,
                    height = 400)

opfig1.update_layout(legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01))

opfig1.update_xaxes(ticks = 'outside')
opfig1.update_yaxes(ticks = 'outside')

opfig1.update_layout(title_x=0.5, title=dict(font=dict(size=30)))
opfig1.show()

opfig2 = px.scatter(overalltotals, x= 'Combined_accommodation', y = 'Total_opioid_toxicity_deaths',
                    trendline = 'ols',
                    labels = {'Total_opioid_toxicity_deaths':'<b>Total Opioid Deaths<b>', 'Combined_accommodation':'<b>Combined Accomodation CPI<b>'},
                    title = '<b>Total Opioid Deaths as a Function of Owned Accomodation CPI<b>',
                    width =1100,
                    height = 400)

opfig2.update_layout(legend=dict(yanchor="top", y=0.99, xanchor="left", x=0.01))

opfig2.update_xaxes(ticks = 'outside')
opfig2.update_yaxes(ticks = 'outside')

opfig2.update_layout(title_x=0.5, title=dict(font=dict(size=30)))

opfig2.show()

totalsperregion = SQL("""select Region,
                        Year_Quarter,
                        Opiates.Region_Year_Quarter,
                        sum(Owned_accommodation) Owned_accommodation,
                        sum(Rented_accommodation) Rented_accommodation,
                        sum(Owned_accommodation) + sum(Rented_accommodation) Both_accommodation,
                        sum(Health_care) as Health_care,
                        sum(Total_opioid_toxicity_deaths) as Total_opioid_toxicity_deaths
                        from Opiates

                        join
                        (select Region_Year_Quarter,
                        avg(Rented_accommodation) Rented_accommodation,
                        avg(Owned_accommodation) Owned_accommodation,
                        avg(Health_care) Health_care
                        from CPI_table_quarter
                        group by Region_Year_Quarter) as CPIavg
                        on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                        where region != 'Whitehorse, Yukon'
                        and region != 'Yellowknife, Northwest Territories'
                        group by Region
                        order by Total_opioid_toxicity_deaths desc""")

opfig3 = px.bar(totalsperregion,
                x= 'Region',
                y=['Total_opioid_toxicity_deaths','Both_accommodation'],
                title = '<b>Total Opioid Deaths Per Quarter<b>',
                labels = {'Total_opioid_toxicity_deaths':'<b>Total Opioid Deaths<b>', 'Year_Quarter':'<b>Year and Quarter<b>','value':''},
                width =1100,
                height = 500,
                barmode='group')

opfig3.update_xaxes(nticks=10, ticks = 'outside')
opfig3.update_yaxes(ticks = 'outside')

opfig3.update_layout(title_x=0.5, title=dict(font=dict(size=30)))

opfig3.show()

databydate = SQL("""select region,
                    Year_Quarter,
                    avg(Owned_accommodation) Owned_accommodation,
                    avg(Rented_accommodation) Rented_accommodation,
                    (avg(Rented_accommodation)+avg(Owned_accommodation))/2 Combined_accommodation,
                    avg(Health_care) as Health_care,
                    sum(Total_opioid_toxicity_deaths) as Total_opioid_toxicity_deaths
                    from Opiates

                    join (select Region_Year_Quarter,
                    avg(Rented_accommodation) Rented_accommodation,
                    avg(Owned_accommodation) Owned_accommodation,
                    avg(Health_care) Health_care
                    from CPI_table_quarter
                    group by Region_Year_Quarter) as CPIavg
                    on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                    where region != 'Whitehorse, Yukon'
                    and region != 'Yellowknife, Northwest Territories'
                    group by Opiates.Region_Year_Quarter""")

opfig4 = px.scatter(databydate, x= 'Combined_accommodation', y = 'Total_opioid_toxicity_deaths',
                    color = 'region',
                    labels = {'Total_opioid_toxicity_deaths':'<b>Total Opioid Deaths<b>', 'Combined_accommodation':'<b>Combined Accomodation CPI<b>', 'region':'Region'},
                    title = '<b>Total Opioid Deaths as a Function of Combined Accomodation CPI<b>',
                    trendline = 'ols',
                    width =1100,
                    height = 400)

opfig4.update_xaxes(ticks = 'outside')
opfig4.update_yaxes(ticks = 'outside')

opfig4.update_layout(title_x=0.5, title=dict(font=dict(size=30)))

opfig4.show()

databydateexcl = SQL("""select region,
                        Year_Quarter,
                        avg(Owned_accommodation) Owned_accommodation,
                        avg(Rented_accommodation) Rented_accommodation,
                        (avg(Rented_accommodation)+avg(Owned_accommodation))/2 Combined_accommodation,
                        avg(Health_care) as Health_care,
                        sum(Total_opioid_toxicity_deaths) as Total_opioid_toxicity_deaths
                        from Opiates

                        join
                        (select Region_Year_Quarter,
                        avg(Rented_accommodation) Rented_accommodation,
                        avg(Owned_accommodation) Owned_accommodation,
                        avg(Health_care) Health_care
                        from CPI_table_quarter
                        group by Region_Year_Quarter) as CPIavg
                        on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                        where region != 'Whitehorse, Yukon'
                        and region != 'Yellowknife, Northwest Territories'
                        and region != 'Ontario'
                        and region != 'British Columbia'
                        and region != 'Quebec'
                        and region != 'Alberta'
                        group by Opiates.Region_Year_Quarter""")

opfig5 = px.scatter(databydateexcl, x= 'Combined_accommodation', y = 'Total_opioid_toxicity_deaths',
                    color = 'region',
                    labels = {'Total_opioid_toxicity_deaths':'<b>Total Opioid Deaths<b>', 'Combined_accommodation':'<b>Combined Accomodation CPI<b>', 'region':'Region'},
                    title = '<b>Total Opioid Deaths as a Function of Combined Accomodation CPI<b>',
                    trendline = 'ols',
                    width =1100,
                    height = 400)

opfig5.update_xaxes(ticks = 'outside')
opfig5.update_yaxes(ticks = 'outside')

opfig5.update_layout(title_x=0.5, title=dict(font=dict(size=30)))

opfig5.show()

databydate6 = SQL("""select region,
                     Year_Quarter,
                     avg(Owned_accommodation) Owned_accommodation,
                     avg(Rented_accommodation) Rented_accommodation,
                     (avg(Rented_accommodation)+avg(Owned_accommodation))/2 Combined_accommodation,
                     avg(Health_care) as Health_care,
                     sum(Total_opioid_toxicity_deaths) as Total_opioid_toxicity_deaths
                     from Opiates

                     join
                     (select Region_Year_Quarter,
                     avg(Rented_accommodation) Rented_accommodation,
                     avg(Owned_accommodation) Owned_accommodation,
                     avg(Health_care) Health_care
                     from CPI_table_quarter
                     group by Region_Year_Quarter) as CPIavg
                     on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                     where region = 'Manitoba'
                     or region = 'Ontario'
                     or region = 'British Columbia'
                     or region = 'Quebec'
                     or region = 'Alberta'
                     or region = 'Saskatchewan'
                     group by Opiates.Region_Year_Quarter""")

opfig6 = px.scatter(databydate6, x= 'Combined_accommodation', y = 'Total_opioid_toxicity_deaths',
                    color = 'region',
                    labels = {'Total_opioid_toxicity_deaths':'<b>Total Opioid Deaths<b>', 'Combined_accommodation':'<b>Combined Accomodation CPI<b>', 'region':'Region'},
                    title = '<b>Total Opioid Deaths as a Function of Combined Accomodation CPI<b>',
                    trendline = 'ols',
                    width =1100,
                    height = 400)

opfig6.update_xaxes(ticks = 'outside')
opfig6.update_yaxes(ticks = 'outside')

opfig6.update_layout(title_x=0.5, title=dict(font=dict(size=30)))

opfig6.show()

correlationdatabc = SQL("""select *
                        from Opiates

                        join
                        (select Region_Year_Quarter,
                        avg(Rented_accommodation) Rented_accommodation,
                        avg(Owned_accommodation) Owned_accommodation
                        from CPI_table_quarter
                        group by Region_Year_Quarter) as CPIavg
                        on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                        where region = 'British Columbia'""")

allcordatabc = correlationdatabc.drop(columns = ["Region","Year_Quarter","Region_Year_Quarter","Accidental_stimulant_deaths","Accidental_stimulant_poisoning_ED_visits","Accidental_stimulant_poisoning_hospitalizations","Total_stimulant_poisoning_ED_visits"], inplace=True)
allcordatabc = correlationdatabc.filter(['Total_opioid_toxicity_deaths', 'Rented_accommodation','Owned_accommodation', 'Health_care'])

opfig7 = px.imshow(allcordatabc.corr(), color_continuous_scale='PuBu')
opfig7.show()

databydateBC = SQL("""select region,
                      Year_Quarter,
                      avg(Owned_accommodation) Owned_accommodation,
                      avg(Rented_accommodation) Rented_accommodation,
                      (avg(Rented_accommodation)+avg(Owned_accommodation))/2 Combined_accommodation,
                      avg(Health_care) as Health_care,
                      sum(Total_opioid_toxicity_deaths) as Total_opioid_toxicity_deaths
                      from Opiates

                      join
                      (select Region_Year_Quarter,
                      avg(Rented_accommodation) Rented_accommodation,
                      avg(Owned_accommodation) Owned_accommodation,
                      avg(Health_care) Health_care
                      from CPI_table_quarter
                      group by Region_Year_Quarter) as CPIavg
                      on Opiates.Region_Year_Quarter=CPIavg.Region_Year_Quarter

                      where region = 'British Columbia'
                      group by Opiates.Region_Year_Quarter""")

fig = go.Figure()
fig = make_subplots(specs=[[{"secondary_y": True}]])

fig.add_trace(go.Scatter(
    x=databydateBC['Year_Quarter'],
    y=databydateBC['Rented_accommodation'],
    name='Rented Accomodation Price'),
    secondary_y=False)

fig.add_trace(go.Scatter(
    x=databydateBC['Year_Quarter'],
    y=databydateBC['Owned_accommodation'],
    name='Owned Accomodation Price'),
    secondary_y=False)

fig.add_trace(go.Scatter(
    x=databydateBC['Year_Quarter'],
    y=databydateBC['Total_opioid_toxicity_deaths'],
    name='Total Opioid Toxicity Deaths'),
    secondary_y=True)

fig.add_trace(go.Scatter(
    x=databydateBC['Year_Quarter'],
    y=databydateBC['Combined_accommodation'],
    name='Combined Accommodation Price'),
    secondary_y=False)

fig.update_layout(legend=dict(yanchor="top",y=0.99, xanchor="left",x=0.01))

fig.update_xaxes(nticks=10, ticks = 'outside', title_text="<b>Year and Quarter<b>")
fig.update_layout(title_text="<b>Opioid Deaths Relationship to Accommodation CPI<b>", title_x=0.5, title=dict(font=dict(size=30)), width=1100, height=500)

fig.update_yaxes(title_text="<b>CPI<b>", ticks = 'outside',secondary_y=False)
fig.update_yaxes(title_text="<b>Total Opioid Deaths</b>", ticks = 'outside',secondary_y=True)

fig.show()

# display(abcordata.iloc[1:3, 0])
# display(bccordata.iloc[1:3, 0])
# display(manitobacordata.iloc[1:3, 0])
# display(newbcordata.iloc[1:3, 0])
# display(nfldcordata.iloc[1:3, 0])
# display(nscordata.iloc[1:3, 0])
# display(oncordata.iloc[1:3, 0])
# display(peicordata.iloc[1:3, 0])
# display(qccordata.iloc[1:3, 0])
# display(saskcordata.iloc[1:3, 0])

general_join = pd.read_sql_query("""SELECT * FROM CPI_table_quarter
INNER JOIN death_table_month ON CPI_table_quarter.year_month_region = death_table_month.YearMonthRegion
WHERE CPI_table_quarter.province = 'Alberta';""", engine)


fig = make_subplots(rows=6, cols=2, subplot_titles=("Cost of Health Care Against Deaths From Accidents and Unintentional Injuries",
                                                    "Cost of Health Care Against Deaths From Alzheimer's Disease",
                                                    "Cost of Health Care Against Deaths From COVID-19",
                                                    "Cost of Health Care Against Deaths From Cerebrovascular Diseases",
                                                    'Cost of Health Care Against Deaths From Chronic Lower Respiratory Disease',
                                                    'Cost of Health Care Against Deaths From Diabetes Mellitus',
                                                    'Cost of Health Care Against Deaths From Diseases of The Heart',
                                                    'Cost of Health Care Against Deaths From Influenza and Pneumonia',
                                                    'Cost of Health Care Against Deaths From Intentional Self Harm',
                                                    'Cost of Health Care Against Deaths From Malignant Neoplasms',
                                                    'Cost of Health Care Against Deaths From Nephritis, Nephrotic Syndrome, and Nephrosis',
                                                   ))


fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Accidents_(Unintentional_Injuries)"], name="Cost of Health Care Against Deaths From Accidents and Unintentional Injuries", mode = "markers"),
    row=1, col=1
)

fig.update_xaxes(title_text="Health Care Costs", row=1, col=1)
fig.update_yaxes(title_text=" Number of Deaths", row=1, col=1)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Alzheimer's disease"], name= "Cost of Health Care Against Deaths From Alzheimer's Disease", mode = "markers"),
    row=1, col=2
)

fig.update_xaxes(title_text="Health Care Costs", row=1, col=2)
fig.update_yaxes(title_text=" Number of Deaths", row=1, col=2)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["COVID-19"], name="Cost of Health Care Against Deaths From COVID-19", mode = "markers"),
    row=2, col=1
)

fig.update_xaxes(title_text="Health Care Costs", row=2, col=1)
fig.update_yaxes(title_text=" Number of Deaths", row=2, col=1)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Cerebrovascular_Diseases"], name="Cost of Health Care Against Deaths From Cerebrovascular Diseases", mode = "markers"),
    row=2, col=2
)

fig.update_xaxes(title_text="Health Care Costs", row=2, col=2)
fig.update_yaxes(title_text=" Number of Deaths", row=2, col=2)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Chronic_Lower_Respiratory_Diseases"], name='Cost of Health Care Against Deaths From Chronic Lower Respiratory Disease', mode = "markers"),
    row=3, col=1
)

fig.update_xaxes(title_text="Health Care Costs", row=3, col=1)
fig.update_yaxes(title_text=" Number of Deaths", row=3, col=1)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Diabetes_Mellitus"], name='Cost of Health Care Against Deaths From Diabetes Mellitus', mode = "markers"),
    row=3, col=2
)

fig.update_xaxes(title_text="Health Care Costs", row=3, col=2)
fig.update_yaxes(title_text=" Number of Deaths", row=3, col=2)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Diseases_of_Heart"], name='Cost of Health Care Against Deaths From Diseases of The Heart', mode = "markers"),
    row=4, col=1
)

fig.update_xaxes(title_text="Health Care Costs", row=4, col=1)
fig.update_yaxes(title_text=" Number of Deaths", row=4, col=1)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Influenza_and_Pneumonia"], name='Cost of Health Care Against Deaths From Influenza and Pneumonia', mode = "markers"),
    row=4, col=2
)

fig.update_xaxes(title_text="Health Care Costs", row=4, col=2)
fig.update_yaxes(title_text=" Number of Deaths", row=4, col=2)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Suicide"], name='Cost of Health Care Against Deaths From Intentional Self Harm', mode = "markers"),
    row=5, col=1
)

fig.update_xaxes(title_text="Health Care Costs", row=5, col=1)
fig.update_yaxes(title_text=" Number of Deaths", row=5, col=1)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Malignant_Neoplasms"], name= 'Cost of Health Care Against Deaths From Malignant Neoplasms', mode = "markers"),
    row=5, col=2
)

fig.update_xaxes(title_text="Health Care Costs", row=5, col=2)
fig.update_yaxes(title_text=" Number of Deaths", row=5, col=2)

fig.add_trace(
    go.Scatter(x=general_join["Health_care"], y=general_join["Nephritis,_Nephrotic_Syndrome_and_Nephrosis"], name= 'Cost of Health Care Against Deaths From Nephritis, Nephrotic Syndrome, and Nephrosis', mode = "markers"),
    row=6, col=1
)

fig.update_xaxes(title_text="Health Care Costs", row=6, col=1)
fig.update_yaxes(title_text=" Number of Deaths", row=6, col=1)

fig.update_layout(title="Cost of Health Care Against Different Causes of Death In Alberta From 1978-2024",showlegend=False, height = 1500, width = 1300)

fig.show()

join_alberta = pd.read_sql_query("""
    SELECT CPI_table_quarter.Health_Care,
           CPI_table_quarter.province,
           death_table_month.`Alzheimer's disease`,
           CPI_table_quarter.Year
    FROM CPI_table_quarter
    INNER JOIN death_table_month
    ON CPI_table_quarter.year_month_region = death_table_month.YearMonthRegion
    WHERE CPI_table_quarter.province = 'Alberta';
""", engine)

join_alberta['Year'] = join_alberta['Year'].dt.strftime('%Y')

join_alberta['Year'] = join_alberta['Year'].astype(int)


fig = px.scatter(
    join_alberta,
    x='Health_Care',
    y="Alzheimer's disease",
    labels={
        'Health_Care': 'Price of Health Care',
        "Alzheimer's_Disease": "Deaths From Alzheimer's Disease"
    },
    title="Price of Health Care Against Deaths From Alzheimer's Disease in Alberta",
    color='Year',  # Pass the column name directly
    color_continuous_scale= 'Bluered_r'  # Continuous color scale
)


fig.show()

join_alberta2 = pd.read_sql_query("""
    SELECT CPI_table_quarter.Health_Care,
           CPI_table_quarter.province,
           death_table_month.`Accidents_(Unintentional_Injuries)`,
           CPI_table_quarter.Year
    FROM CPI_table_quarter
    INNER JOIN death_table_month
    ON CPI_table_quarter.year_month_region = death_table_month.YearMonthRegion
    WHERE CPI_table_quarter.province = 'Alberta';
""", engine)

join_alberta2['Year'] = join_alberta2['Year'].dt.strftime('%Y')

join_alberta2['Year'] = join_alberta2['Year'].astype(int)



fig = px.scatter(
    join_alberta2,
    x='Health_Care',
    y="Accidents_(Unintentional_Injuries)",
    labels={
        'Health_Care': 'Price of Health Care',
        "Accidents_(Unintentional_Injuries)": "Number of Deaths"
    },
    title="Price of Health Care Against Deaths From Accidental Deaths in Alberta",
    color='Year',  # Pass the column name directly
    color_continuous_scale= 'Bluered_r'  # Continuous color scale
)



fig.show()

join_alberta3 = pd.read_sql_query("""
    SELECT CPI_table_quarter.Health_Care,
           CPI_table_quarter.province,
           death_table_month.`Influenza_and_Pneumonia`,
           CPI_table_quarter.Year
    FROM CPI_table_quarter
    INNER JOIN death_table_month
    ON CPI_table_quarter.year_month_region = death_table_month.YearMonthRegion
    WHERE CPI_table_quarter.province = 'Alberta';
""", engine)

join_alberta3['Year'] = join_alberta3['Year'].dt.strftime('%Y')

join_alberta3['Year'] = join_alberta3['Year'].astype(int)



fig = px.scatter(
    join_alberta3,
    x='Health_Care',
    y="Influenza_and_Pneumonia",
    labels={
        'Health_Care': 'Price of Health Care',
        "Influenza_and_Pneumonia": "Number of Deaths"
    },
    title="Price of Health Care Against Deaths From Influenza and Pneumonia in Alberta",
    color='Year',  # Pass the column name directly
    color_continuous_scale= 'Bluered_r'  # Continuous color scale
)



fig.show()

labour_query = pd.read_sql_query("""SELECT quarter, AVG(Unemployment_percent) AS Unemployment_percent
                                    FROM labour_table GROUP BY quarter""",engine)
labour_query.to_sql("labour", engine, if_exists = "replace")
display(pd.read_sql_query("SELECT * FROM labour LIMIT 5", engine))

opiate_death_query = pd.read_sql_query("""SELECT death_table_quarter.RegionYearQuarter, Opiates.Region,
                                        Opiates.Year_Quarter, Opiates.Total_opioid_toxicity_deaths,
                                        death_table_quarter.Suicide FROM Opiates JOIN death_table_quarter
                                        ON death_table_quarter.RegionYearQuarter =
                                        Opiates.Region_Year_Quarter""",engine)
opiate_death_query.to_sql("opiate_death", engine, if_exists = "replace")
display(pd.read_sql_query("SELECT * FROM opiate_death LIMIT 5", engine))

join_query = pd.read_sql_query("""SELECT opiate_death.Region, opiate_death.Year_Quarter,
                                opiate_death.Total_opioid_toxicity_deaths,
                                opiate_death.Suicide, labour.Unemployment_percent FROM opiate_death
                                JOIN labour ON opiate_death.RegionYearQuarter = labour.quarter""",engine)
join_query.to_sql("final_table", engine, if_exists = "replace")
display(pd.read_sql_query("SELECT * FROM final_table LIMIT 5", engine))

fig = px.scatter(join_query,
                    x='Unemployment_percent',
                    y='Suicide',
                    title='Unemployment Rate vs # of Suicide Related Deaths per Quarter in Canada from 2016-2024',
                    labels={'Unemployment_percent': 'Unemployment Rate (%)', 'Suicide': '# of Suicide Related Deaths'},
                    width = 1000,
                    height = 700)
fig.show()

fig = px.scatter(join_query,
                    x='Unemployment_percent',
                    y='Total_opioid_toxicity_deaths',
                    title='Unemployment Rate vs # of Opioid Related Deaths per Quarter in Canada from 2016-2024',
                    labels={'Unemployment_percent': 'Unemployment Rate (%)', 'Total_opioid_toxicity_deaths': '# of Opioid Related Deaths'},
                    width = 1000,
                    height = 700)
fig.show()

# unpivot the joined table
unpivotted_deaths = pd.melt(join_query, id_vars=['Year_Quarter'], value_vars=['Total_opioid_toxicity_deaths', 'Suicide'])
# upload table into SQL
unpivotted_deaths.to_sql("unpivotted_deaths", engine, if_exists = "replace")

660

# create query to sum number of deaths (Canada) for each cause of death (suicide and opioid deaths)
canada_query = pd.read_sql_query("""SELECT Year_Quarter, variable, SUM(value)
            FROM unpivotted_deaths GROUP BY Year_Quarter, variable""", engine)

fig = px.line(canada_query,
                    x='Year_Quarter',
                    y='SUM(value)',
                    color='variable',
                    title='Number of Deaths per Quarter in Canada from 2016-2024',
                    labels={'Year_Quarter': 'Yearly Quarter', 'SUM(value)': '# of Deaths','variable':'Cause of Death'},
                    width = 1000,
                    height = 700)
fig.show()

# join CPI and labour on month and date
cpilab_fulljoin = pdsql("""
SELECT * FROM
    CPI_table_quarter t1 INNER JOIN labour_table t2 ON t1.year_month_region = t2.dateregion
""")

cpilab_fulljoin.head()

cpilab_fulljoin["TotalCPI"] = cpilab_fulljoin["Goods"] + cpilab_fulljoin["Services"]

cpilab_fulljoin["recreation_percent"] = 100 * cpilab_fulljoin["Recreation"] / cpilab_fulljoin["TotalCPI"]

# PLOT: recreation cost against participation, for alberta
multi_line_plot(
    cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta'],
    columntuples = [('date', 'Participation_percent'), ('date','recreation_percent')],
    serieslabels = ['Participation Rate (%)', 'Recreation CPI / Total CPI (%)'],
    xlabel = 'Date',
    ylabel = "Percentage",
    title = "Recreation and Participation Rate over time in Alberta"
)

# PLOT: restaurant cost against unemployment, for alberta
custom_scatter(
    xdata = cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Unemployment_percent'],
    ydata = 100* cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']["Food__purchased_from_restaurants"]
                / cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']["TotalCPI"],
    colordata = [int(x.strftime('%Y')) for x in cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['date']],
    cbartitle = 'Year',
    titlelabel = 'Alberta Restaurant Prices vs Unemployment',
    xlabel = 'Unemployment (%)',
    ylabel = 'Food From Restaurants CPI / Total CPI (%)',
    xrange = [0,20]
)

# PLOT -> grocery prices vs unemployment
custom_scatter(
    xdata = cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Unemployment_percent'],
    ydata = 100* cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Food_purchased_from_stores']
               / cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['TotalCPI'],
    colordata = [int(x.strftime('%Y')) for x in cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['date']],
    cbartitle = 'Year',
    titlelabel = 'Alberta Grocery Prices vs Unemployment',
    xlabel = 'Unemployment (%)',
    ylabel = 'Food From Stores CPI / Total CPI (%)',
    xrange = [0,20]
)

# PLOT -> rental prices vs unemployment
custom_scatter(
    xdata = cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Unemployment_percent'],
    ydata = 100* cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Rented_accommodation']
               / cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['TotalCPI'],
    colordata = [int(x.strftime('%Y')) for x in cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['date']],
    cbartitle = 'Year',
    titlelabel = 'Alberta Rental Prices vs Unemployment',
    xlabel = 'Unemployment (%)',
    ylabel = 'Rented Accommodation CPI / Total CPI (%)',
    xrange = [0,20]
)

# correlation matrix for Alberta (rows are CPI, columns are Labour)
# select only numeric columns (no region, date, etc)
cpilabnumeric = cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta'].select_dtypes(include='float64')
cpilabcorrelation = cpilabnumeric.corr() # calculate correlations
cpilabcorrelation.iloc[:23,23:-3].style.background_gradient(cmap='coolwarm') # select which rows/cols and color

# PLOT -> clothing prices vs unemployment
custom_scatter(
    xdata = cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Unemployment_percent'],
    ydata = cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['Clothing']
               / cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['TotalCPI'],
    colordata = [int(x.strftime('%Y')) for x in cpilab_fulljoin[cpilab_fulljoin.region == 'Alberta']['date']],
    cbartitle = 'Year',
    titlelabel = 'Alberta Clothing Prices vs Unemployment',
    xlabel = 'Unemployment (%)',
    ylabel = 'Clothing CPI / Total CPI (%)',
    xrange = [0,20]
)

# clear up resources
engine.dispose()

	Region	Year_Quarter	Region_Year_Quarter	Accidental_opioid_deaths	Accidental_stimulant_deaths	Accidental_opioid_poisoning_ED_visits	Accidental_opioid_poisoning_hospitalizations	Accidental_stimulant_poisoning_ED_visits	Accidental_stimulant_poisoning_hospitalizations	EMS_responses_to_opioid_overdoses	Total_opioid_toxicity_deaths	Total_stimulant_toxicity_deaths	Total_opioid_poisoning_ED_visits	Total_opioid_poisoning_hospitalizations	Total_stimulant_poisoning_ED_visits	Total_stimulant_poisoning_hospitalizations
0	Alberta	2016 Q1	Alberta2016Q1	118.0	NaN	504.0	135.0	291.0	39.0	NaN	137.0	NaN	757.0	229.0	433.0	80.0
1	Alberta	2016 Q2	Alberta2016Q2	130.0	NaN	649.0	140.0	392.0	54.0	NaN	140.0	NaN	918.0	239.0	564.0	101.0
2	Alberta	2016 Q3	Alberta2016Q3	144.0	NaN	700.0	156.0	383.0	51.0	NaN	159.0	NaN	948.0	239.0	551.0	97.0
3	Alberta	2016 Q4	Alberta2016Q4	155.0	NaN	723.0	127.0	348.0	47.0	NaN	166.0	NaN	1002.0	223.0	510.0	92.0
4	Alberta	2017 Q1	Alberta2017Q1	152.0	NaN	786.0	151.0	356.0	54.0	567.0	162.0	NaN	1027.0	235.0	520.0	100.0
...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...	...
424	Yukon	2023 Q1	Yukon2023Q1	3.0	2.0	10.0	NaN	NaN	NaN	NaN	3.0	2.0	12.0	NaN	NaN	NaN
425	Yukon	2023 Q2	Yukon2023Q2	5.0	5.0	28.0	NaN	7.0	NaN	NaN	5.0	5.0	30.0	NaN	8.0	NaN
426	Yukon	2023 Q3	Yukon2023Q3	2.0	2.0	9.0	NaN	5.0	NaN	NaN	2.0	2.0	10.0	NaN	6.0	NaN
427	Yukon	2023 Q4	Yukon2023Q4	7.0	7.0	6.0	NaN	NaN	NaN	NaN	7.0	7.0	9.0	NaN	NaN	NaN
428	Yukon	2024 Q1	Yukon2024Q1	4.0	2.0	10.0	NaN	7.0	NaN	NaN	4.0	2.0	17.0	NaN	9.0	NaN

	year_quarter	Total_Deaths
0	2023 Q2	2176
1	2021 Q4	2171
2	2023 Q4	2137
3	2023 Q1	2075
4	2023 Q3	2071
5	2022 Q4	2060
6	2022 Q1	2036
7	2021 Q3	1951
8	2024 Q1	1906
9	2021 Q1	1883
10	2021 Q2	1874
11	2020 Q4	1870
12	2022 Q3	1848
13	2022 Q2	1832
14	2020 Q3	1788
15	2020 Q2	1686
16	2018 Q4	1128
17	2018 Q3	1100
18	2019 Q2	1060
19	2017 Q3	1057

	Region_Year_Quarter	Total_opioid_toxicity_deaths
0	Ontario2021Q1	735
1	Ontario2021Q2	733
2	Ontario2020Q4	725
3	Ontario2021Q4	709
4	Ontario2023Q2	691
...	...	...
93	Alberta2016Q1	137
94	Quebec2022Q2	136
95	Quebec2023Q3	127
96	Quebec2023Q2	126
97	Alberta2019Q4	125

	Suspected intentional opioid overdoses
0	Alberta has a total of 181 suspected intention...
1	British Columbia has a total of 268 suspected ...
2	Canada has a total of 2575 suspected intention...
3	Manitoba has a total of 244 suspected intentio...
4	New Brunswick has a total of 52 suspected inte...
5	Newfoundland and Labrador has a total of 21 su...
6	Northwest Territories has a total of 1 suspect...
7	Nova Scotia has a total of 75 suspected intent...
8	Ontario has a total of 1146 suspected intentio...
9	Prince Edward Island has a total of 0 suspecte...
10	Quebec has a total of 464 suspected intentiona...
11	Saskatchewan has a total of 79 suspected inten...
12	Yukon has a total of 2 suspected intentional o...

	REF_DATE	GEO	DGUID	product	UOM	UOM_ID	SCALAR_FACTOR	VECTOR	COORDINATE	VALUE	DECIMALS
0	1978-10	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	48.1	1
1	1978-11	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	48.1	1
2	1978-12	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	48.2	1
3	1979-01	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	48.8	1
4	1979-02	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	49.8	1
5	1979-03	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	51.0	1
6	1979-04	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	52.1	1
7	1979-05	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	53.2	1
8	1979-06	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	53.4	1
9	1979-07	Newfoundland and Labrador	2016A000210	Food purchased from stores	2002=100	17	units	v41691246	3.4	55.0	1

Health and Socioeconomic Factors Across Canada¶

Data 604 Final Report¶

Group Members

Setup¶

Introduction¶

Individual Datasets¶

Monthly Consumer Price Index (Kennedy)¶

Monthly Labour Force Characteristics (Noah)¶

Cleaning¶

Data Exploration¶

Provisional Weekly Death Counts (Bobbi)¶

Cleaning the Data Frame¶

Exploring the Data¶

Data exploration¶

Are there trends in the cost of healthcare in relation to causes of death?¶

Is there fluctuation in the price of goods as unemployment rates change, and does this differs between recreational goods such as alcohol versus necessities such as shelter?¶

Guiding Question Conclusion¶

Discussion¶

Conclusion¶

References¶

	Region	Total_Opioid_Deaths
0	Ontario	16300
1	British Columbia	14260
2	Alberta	9363
3	Quebec	2816
4	Saskatchewan	1637
5	Manitoba	1520
6	Nova Scotia	472
7	New Brunswick	405
8	Newfoundland and Labrador	157
9	Yukon	96
10	Northwest Territories	26
11	Prince Edward Island	6
12	Nunavut	0

	dateregion	date	region	Population_thousands	LabourForce_thousands	Employment_thousands	Fulltime_thousands	Parttime_thousands	Unemployment_thousands	Unemployment_percent	Participation_percent	Employment_percent	quarter
0	1976-01:Alberta	1976-01-01	Alberta	1276.7	832.0	787.8	653.3	134.5	44.2	5.3	65.2	61.7	Alberta1976Q1
1	1976-01:British Columbia	1976-01-01	British Columbia	1852.4	1099.6	988.4	835.8	152.5	111.2	10.1	59.4	53.4	British Columbia1976Q1
2	1976-01:Canada	1976-01-01	Canada	16852.4	10087.1	9271.4	8007.4	1264.0	815.7	8.1	59.9	55.0	Canada1976Q1
3	1976-01:Manitoba	1976-01-01	Manitoba	732.9	441.4	411.8	348.9	63.0	29.6	6.7	60.2	56.2	Manitoba1976Q1
4	1976-01:New Brunswick	1976-01-01	New Brunswick	476.8	244.1	214.1	185.5	28.6	30.0	12.3	51.2	44.9	New Brunswick1976Q1

	region	AvgUnemployment
0	Saskatchewan	5.892479
1	Manitoba	6.293504
2	Alberta	6.559487
3	Ontario	7.395214
4	Canada	8.066154
5	British Columbia	8.085641
6	Quebec	9.281880
7	Nova Scotia	10.060513
8	New Brunswick	10.900342
9	Prince Edward Island	11.999658
10	Newfoundland and Labrador	15.589573

	Region	Year	AverageEmploymentRate
0	Newfoundland and Labrador	2013	54.416667
1	Nova Scotia	2008	58.858333
2	New Brunswick	2008	59.341667
3	Prince Edward Island	2013	61.983333
4	Quebec	2023	62.066667
5	British Columbia	2007	63.308333
6	Canada	2008	63.341667
7	Manitoba	2008	66.416667
8	Ontario	1989	66.458333
9	Saskatchewan	2013	67.358333
10	Alberta	2008	71.916667

	region	avg2019	avg2020	diff
0	Alberta	71.700000	68.941667	2.758333
1	Newfoundland and Labrador	58.741667	56.458333	2.283333
2	Prince Edward Island	67.008333	64.808333	2.200000
3	British Columbia	66.333333	64.216667	2.116667
4	Nova Scotia	62.258333	60.425000	1.833333
5	Saskatchewan	69.091667	67.275000	1.816667

	REF_DATE	GEO	DGUID	Cause of death	Characteristics	UOM	UOM_ID	SCALAR_FACTOR	VECTOR	COORDINATE	VALUE	STATUS	SYMBOL	TERMINATED
0	2010-01-09	Newfoundland and Labrador, place of occurrence	2016A000210	Malignant neoplasms [C00-C97]	Number of deaths	Number	223	units	v1234858198	2.2.1	25.0	NaN	NaN	NaN
1	2010-01-16	Newfoundland and Labrador, place of occurrence	2016A000210	Malignant neoplasms [C00-C97]	Number of deaths	Number	223	units	v1234858198	2.2.1	35.0	NaN	NaN	NaN
2	2010-01-23	Newfoundland and Labrador, place of occurrence	2016A000210	Malignant neoplasms [C00-C97]	Number of deaths	Number	223	units	v1234858198	2.2.1	20.0	NaN	NaN	NaN
3	2010-01-30	Newfoundland and Labrador, place of occurrence	2016A000210	Malignant neoplasms [C00-C97]	Number of deaths	Number	223	units	v1234858198	2.2.1	20.0	NaN	NaN	NaN
4	2010-02-06	Newfoundland and Labrador, place of occurrence	2016A000210	Malignant neoplasms [C00-C97]	Number of deaths	Number	223	units	v1234858198	2.2.1	25.0	NaN	NaN	NaN

	Region	Cause_of_Death	Number_of_Deaths	Quarterly	Monthly	RegionYearQuarter	YearMonthRegion
0	Newfoundland and Labrador	Malignant neoplasms	25.0	2010Q1	2010-01	Newfoundland and Labrador2010Q1	2010-01:Newfoundland and Labrador
1	Newfoundland and Labrador	Malignant neoplasms	35.0	2010Q1	2010-01	Newfoundland and Labrador2010Q1	2010-01:Newfoundland and Labrador
2	Newfoundland and Labrador	Malignant neoplasms	20.0	2010Q1	2010-01	Newfoundland and Labrador2010Q1	2010-01:Newfoundland and Labrador
3	Newfoundland and Labrador	Malignant neoplasms	20.0	2010Q1	2010-01	Newfoundland and Labrador2010Q1	2010-01:Newfoundland and Labrador
4	Newfoundland and Labrador	Malignant neoplasms	25.0	2010Q1	2010-02	Newfoundland and Labrador2010Q1	2010-02:Newfoundland and Labrador

	RegionYearQuarter	Cause_of_Death	Number_of_Deaths
0	Alberta2010Q1	Accidents (unintentional injuries)	155.0
1	Alberta2010Q1	Alzheimer's disease	110.0
2	Alberta2010Q1	COVID-19	0.0
3	Alberta2010Q1	Cerebrovascular diseases	240.0
4	Alberta2010Q1	Chronic lower respiratory diseases	220.0

Cause_of_Death	Accidents_(Unintentional_Injuries)	Alzheimer's_Disease	COVID-19	Cerebrovascular_Diseases	Chronic_Lower_Respiratory_Diseases	Diabetes_Mellitus	Diseases_of_Heart	Influenza_and_Pneumonia	Suicide	Malignant_Neoplasms	Nephritis,_Nephrotic_Syndrome_and_Nephrosis
RegionYearQuarter
Alberta2010Q1	155.0	110.0	0.0	240.0	220.0	105.0	1185.0	70.0	105.0	1310.0	80.0
Alberta2010Q2	195.0	90.0	0.0	280.0	240.0	85.0	1185.0	100.0	135.0	1340.0	75.0
Alberta2010Q3	240.0	80.0	0.0	265.0	195.0	80.0	1080.0	90.0	170.0	1465.0	60.0
Alberta2010Q4	270.0	95.0	0.0	280.0	220.0	95.0	1230.0	105.0	130.0	1400.0	75.0
Alberta2011Q1	195.0	110.0	0.0	285.0	325.0	135.0	1355.0	120.0	115.0	1475.0	70.0

	YearMonthRegion	Cause_of_Death	Number_of_Deaths
0	2010-01:Alberta	Accidents (unintentional injuries)	55.0
1	2010-01:Alberta	Alzheimer's disease	35.0
2	2010-01:Alberta	COVID-19	0.0
3	2010-01:Alberta	Cerebrovascular diseases	90.0
4	2010-01:Alberta	Chronic lower respiratory diseases	60.0

	index	quarter	Unemployment_percent
0	0	Alberta1976Q1	4.800000
1	1	Alberta1976Q2	3.800000
2	2	Alberta1976Q3	3.566667
3	3	Alberta1976Q4	3.700000
4	4	Alberta1977Q1	5.366667

	year_month_region	Food_purchased_from_stores	Food__purchased_from_restaurants	Rented_accommodation	Owned_accommodation	Water_fuel_and_electricity	Household_operations	Household_furnishings_and_equipment	Clothing	Footwear	...	Population_thousands	LabourForce_thousands	Employment_thousands	Fulltime_thousands	Parttime_thousands	Unemployment_thousands	Unemployment_percent	Participation_percent	Employment_percent	quarter
0	1978-09:Alberta	42.2	36.2	47.7	43.0	23.8	41.9	52.3	NaN	55.9	...	1484.5	1017.9	971.7	834.2	137.5	46.3	4.5	68.6	65.5	Alberta1978Q3
1	1978-09:British Columbia	41.0	35.0	45.9	61.9	33.1	39.8	49.5	NaN	47.5	...	1983.3	1251.0	1162.1	989.6	172.5	88.9	7.1	63.1	58.6	British Columbia1978Q3
2	1978-09:Manitoba	43.2	34.2	45.5	41.2	31.3	39.3	45.3	NaN	42.4	...	755.3	478.4	447.6	382.7	64.8	30.8	6.4	63.3	59.3	Manitoba1978Q3
3	1978-09:New Brunswick	42.9	33.8	48.6	43.6	28.7	41.4	54.2	NaN	51.5	...	500.1	282.6	253.2	224.2	29.0	29.3	10.4	56.5	50.6	New Brunswick1978Q3
4	1978-09:Newfoundland and Labrador	NaN	36.2	53.7	43.5	31.1	46.7	55.7	NaN	53.8	...	383.2	208.6	180.0	164.8	15.2	28.6	13.7	54.4	47.0	Newfoundland and Labrador1978Q3

	Population_thousands	LabourForce_thousands	Employment_thousands	Fulltime_thousands	Parttime_thousands	Unemployment_thousands	Unemployment_percent	Participation_percent
Food_purchased_from_stores	0.983042	0.973369	0.962707	0.949621	0.944780	0.592327	-0.103181	-0.106262
Food__purchased_from_restaurants	0.989953	0.980292	0.970003	0.955271	0.958442	0.592234	-0.118700	-0.113703
Rented_accommodation	0.986066	0.989075	0.983938	0.972313	0.958249	0.547147	-0.165262	0.004779
Owned_accommodation	0.990646	0.985920	0.975818	0.967491	0.936873	0.593251	-0.112225	-0.076395
Water_fuel_and_electricity	0.967121	0.962610	0.962654	0.955216	0.920964	0.484053	-0.210397	-0.063072
Household_operations	0.972622	0.977735	0.969139	0.954626	0.956727	0.574678	-0.120378	0.029188
Household_furnishings_and_equipment	0.733563	0.731778	0.722064	0.692260	0.792731	0.461649	-0.020770	0.042581
Clothing	-0.230552	-0.216890	-0.178751	-0.194896	-0.092290	-0.465478	-0.402347	0.175307
Footwear	0.457617	0.469421	0.461780	0.429022	0.564600	0.309740	0.036137	0.184096
Clothing_accessories_watches_jewellery	0.972415	0.965579	0.954175	0.942341	0.920771	0.538609	-0.187909	-0.282393
Clothing_material_notions_services	0.993039	0.989750	0.979894	0.966946	0.960076	0.592835	-0.122280	-0.059012
Public_transportation	0.981546	0.974417	0.968042	0.956739	0.942198	0.551678	-0.162592	-0.088523
Gasoline	0.938011	0.942250	0.946236	0.945393	0.878044	0.435917	-0.225858	0.022472
Private_transportation	0.978003	0.974161	0.971676	0.959544	0.949053	0.514165	-0.190427	-0.050520
Health_care	0.971659	0.975083	0.967201	0.951916	0.958182	0.566486	-0.130042	0.012459
Personal_care	0.965894	0.960494	0.950837	0.932703	0.955045	0.576203	-0.103747	-0.056431
Recreation	0.832613	0.845995	0.845734	0.825198	0.868012	0.428319	-0.171422	0.151770
Education_and_reading	0.983447	0.981271	0.981373	0.968590	0.949805	0.438444	-0.334741	-0.236719
Alcoholic_beverages	0.972135	0.974639	0.968068	0.952125	0.961752	0.553676	-0.149584	0.006219
Tobacco_smokers_supplies	0.980379	0.966909	0.955765	0.943663	0.934223	0.593726	-0.114581	-0.153437
Recreational_cannabis	-0.687308	-0.482901	-0.421765	-0.370882	-0.406958	0.128972	0.177343	0.473292
Goods	0.948628	0.949313	0.946622	0.930237	0.943793	0.503650	-0.171152	0.009129
Services	0.997018	0.994540	0.986713	0.975869	0.957528	0.575741	-0.144557	-0.052488

Health and Socioeconomic Factors Across Canada¶

Data 604 Final Report¶

Group Members

Setup¶

Introduction¶

Individual Datasets¶

Opioid and Stimulant-Related Harms in Canada (Max)¶

Monthly Consumer Price Index (Kennedy)¶

Monthly Labour Force Characteristics (Noah)¶

Cleaning¶

Data Exploration¶

Provisional Weekly Death Counts (Bobbi)¶

Cleaning the Data Frame¶

Exploring the Data¶

Data exploration¶

Is there a correlation between shelter price and opioid-related deaths?¶

Are there trends in the cost of healthcare in relation to causes of death?¶

Is there correlation between unemployment rates, opioid-related deaths and deaths caused by suicide?¶

Is there fluctuation in the price of goods as unemployment rates change, and does this differs between recreational goods such as alcohol versus necessities such as shelter?¶

Guiding Question Conclusion¶

Discussion¶

Conclusion¶

References¶